#include "dbadapter.h"
#include "iostream"

DBAdapter::DBAdapter(QObject *parent) :
    QObject(parent)
{
    _db="testdb";
}

DBAdapter::DBAdapter(QString dbName, QObject *parent):QObject(parent)
{
    _db=dbName;
}

bool DBAdapter::InitDb()
{
    dbhelper conn;
    conn.GetConnection(_db);
    bool ret = conn.InitDataBase();
    return ret;
}

////基本方法
int DBAdapter::Insert(QString table,QVariantMap map)
{
    dbhelper conn;
    conn.GetConnection(_db);
    return conn.Insert(table,map);
}

bool DBAdapter::Update(QString table, int fid, QVariantMap map)
{
    dbhelper conn;
    conn.GetConnection(_db);
    return conn.Update(table,map,"Fid="+QString::number(fid));
}

QList<QVariantMap> DBAdapter::Select(QString table, QVariantMap map, QStringList columns, QString filter)
{
    dbhelper conn;
    conn.GetConnection(_db);
    return conn.SimpleSelect(table,columns,map,filter);
}

bool DBAdapter::Delete(QString table, QString filter)
{
    dbhelper conn;
    conn.GetConnection(_db);
    return conn.Delete(table,filter);
}

//获取用户余额
qreal DBAdapter::GetUserAsset()
{
    dbhelper conn;
    conn.GetConnection(_db);
    QString strSql =
    "select famount from t_bas_initialassets";
    QVariantMap map;
    QList<QVariantMap> ret1 = conn.CustomSelect(strSql,map);
    double assets;
    if(ret1.count() == 0) assets=0;
    else assets = ret1[0]["famount"].toReal();
    return assets - GetPaymentSum() + GetIncomeSum();
}


///列表数据---收入列表，支出列表,收入类型表，支出类型表
QList<QVariantMap> DBAdapter::GetAllIncome(QDate fromDate, QDate toDate, int UserId)
{
    dbhelper conn;
    conn.GetConnection(_db);
    QString strSql =
            "SELECT "
            "A.FID,A.FNUMBER,A.FAMOUNT,FITYPEID,B.FNAME AS FTYPENAME,FDATE,FYEAR,FMONTH,FDAY,A.FDESCRIPTION "
            "FROM "
            "T_BAS_INCOME A INNER JOIN T_BAS_INCOMETYPE B ON A.FITYPEID = B.FID ";
    QString timeFilter = ToolHelper::GetDateFilter(fromDate,toDate);
    strSql = strSql + timeFilter + " AND A.FUSERID="+QString::number(UserId);
    std::cout<<strSql.toStdString()<<std::endl;
    return conn.CustomSelect(strSql,QVariantMap());
}

QList<QVariantMap> DBAdapter::GetAllPayment(QDate fromDate, QDate toDate, int UserId)
{
    dbhelper conn;
    conn.GetConnection(_db);
    QString strSql =
            "SELECT "
            "A.FID,A.FNUMBER,A.FAMOUNT,FPTYPEID,B.FNAME AS FTYPENAME,FDATE,FYEAR,FMONTH,FDAY,A.FDESCRIPTION "
            "FROM "
            "T_BAS_PAYMENT A INNER JOIN T_BAS_PAYMENTTYPE B ON A.FPTYPEID = B.FID ";
    QString timeFilter = ToolHelper::GetDateFilter(fromDate,toDate);
    strSql = strSql + timeFilter + " AND A.FUSERID="+QString::number(UserId);
    return conn.CustomSelect(strSql,QVariantMap());
}

QList<QVariantMap> DBAdapter::GetIncomeType()
{
    dbhelper conn;
    QList<QVariantMap> ret;
    conn.GetConnection(_db);
    ret = conn.SimpleSelect("T_BAS_INCOMETYPE",QStringList("*"),QVariantMap(),QString());
    return ret;
}

QList<QVariantMap> DBAdapter::GetPaymentType()
{
    dbhelper conn;
    conn.GetConnection(_db);
    return conn.SimpleSelect("T_BAS_PAYMENTTYPE",QStringList("*"),QVariantMap(),QString());
}

///复杂报表

//饼图报表，按照收入/支出类型获取每个类型的 总和，全部总和，占有百分比
QList<QVariantMap> DBAdapter::GetIncomeRptGroupByType(QDate fromDate,QDate toDate,int UserId)
{
    dbhelper conn;
    conn.GetConnection(_db);
    return bizQueryHelper::GetIncomeRptGroupByType(conn,fromDate,toDate,UserId);
}

QList<QVariantMap> DBAdapter::GetPaymentRptGroupByType(QDate fromDate,QDate toDate,int UserId)
{
    dbhelper conn;
    conn.GetConnection(_db);
    return bizQueryHelper::GetPaymentRptGroupByType(conn,fromDate,toDate,UserId);
}

//时间段内收入/支出报表
QList<QVariantMap> DBAdapter::GetIncomeRpt(QDate fromDate,QDate toDate,int UserId,QString filter)
{
    dbhelper conn;
    conn.GetConnection(_db);
    QStringList filters;
    filters.append(ToolHelper::GetDateFilter(fromDate,toDate));
    filters.append(" FUserId="+QString::number(UserId));
    if(!filter.isEmpty()) filters.append(filter);
    return bizQueryHelper::GetIncomeRpt(conn,filters.join(" And ") + " group by FYEAR,FMONTH");
}

QList<QVariantMap> DBAdapter::GetPaymentRpt(QDate fromDate,QDate toDate,int UserId,QString filter)
{
    dbhelper conn;
    conn.GetConnection(_db);
    QStringList filters;
    filters.append(ToolHelper::GetDateFilter(fromDate,toDate));
    filters.append(" FUserId="+QString::number(UserId));
    if(!filter.isEmpty()) filters.append(filter);
    return bizQueryHelper::GetPaymentRpt(conn,filters.join(" And ") + " group by FYEAR,FMONTH");
}

QList<QVariantMap> DBAdapter::GetdayPaymentRpt(QDate d,int UserId,QString filter)
{
    dbhelper conn;
    conn.GetConnection(_db);
    QStringList filters;
    QString year = QString::number(d.year());
    QString month = QString::number(d.month());
    filters.append(" Where FYear="+year);
    filters.append(" FMonth="+month);
    filters.append(" FUserId="+QString::number(UserId));
    if(!filter.isEmpty()) filters.append(filter);
    QString strSql = "SELECT SUM(FAMOUNT) as FSUM,FYEAR,FMONTH,FDAY FROM T_BAS_PAYMENT" + filters.join(" And ") + " group by FDAY";
    return conn.CustomSelect(strSql,QMap<QString,QVariant>());
}

QDateTime DBAdapter::GetLastDate()
{
    dbhelper conn;
    conn.GetConnection(_db);
    QString strSql = "select max(fdate) as fmax from (select fdate from t_bas_payment"
            " union all"
            " select fdate from t_bas_income) as t1 ";
    QList<QVariantMap> ret = conn.CustomSelect(strSql,QMap<QString,QVariant>());
    if(ret.count()>0)
    {
        int v = ret[0]["fmax"].toInt();
        int year = v/10000;
        int month = v%10000/100;
        int day = v%100;
        QDate d(year,month,day);
        return QDateTime(d);
    }
    else
        return QDateTime::currentDateTime();
}

int DBAdapter::GetPaymentCount()
{
    dbhelper conn;
    conn.GetConnection(_db);
    QString strSql = "SELECT COUNT(FID) FCOUNT FROM T_BAS_PAYMENT";
    QList<QVariantMap> ret = conn.CustomSelect(strSql,QMap<QString,QVariant>());
    if(ret.count() != 0)
      return ret[0]["FCOUNT"].toInt();
    else 
      return 0;
}

int DBAdapter::GetIncomeCount()
{
    dbhelper conn;
    conn.GetConnection(_db);
    QString strSql = "SELECT COUNT(FID) FCOUNT FROM T_BAS_INCOME";
    QList<QVariantMap> ret = conn.CustomSelect(strSql,QMap<QString,QVariant>());
    if(ret.count() != 0)
      return ret[0]["FCOUNT"].toInt();
    else 
      return 0;
}

qreal DBAdapter::GetIncomeSum()
{
    dbhelper conn;
    conn.GetConnection(_db);
    QString strSql = "SELECT SUM(FID) FSUM FROM T_BAS_INCOME";
    QList<QVariantMap> ret = conn.CustomSelect(strSql,QMap<QString,QVariant>());
    if(ret.count() != 0)
      return ret[0]["FSUM"].toReal();
    else 
      return 0;
}

qreal DBAdapter::GetPaymentSum()
{
    dbhelper conn;
    conn.GetConnection(_db);
    QString strSql = "SELECT SUM(FID) FSUM FROM T_BAS_PAYMENT";
    QList<QVariantMap> ret = conn.CustomSelect(strSql,QMap<QString,QVariant>());
    if(ret.count() != 0)
      return ret[0]["FSUM"].toReal();
    else 
      return 0;
}